﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using OfficeOpenXml;

namespace readwriteexcel
{
    class Program
    {
        static void Main(string[] args)
        {
            string filePath = @"C:\Users\richardjiang\Desktop\初心者.xlsx";
            Test2(filePath);
//            TestEpp(filePath);
        }

        private static void Test2(string path)
        {
            using (ExcelPackage package = new ExcelPackage(new FileStream(path, FileMode.Open)))
            {
                for (int i = 1; i <= package.Workbook.Worksheets.Count; ++i)
                {
                    ExcelWorksheet sheet = package.Workbook.Worksheets[i];
                    for (int j = sheet.Dimension.Start.Column, k = sheet.Dimension.End.Column; j <= k; j++)
                    {
                        for (int m = sheet.Dimension.Start.Row, n = sheet.Dimension.End.Row; m <= n; m++)
                        {
                            string str = sheet.Cells[j, m].Value?.ToString();
                            if (str != null)
                            {
                                Console.WriteLine(str);
                                // do something
                            }
                        }
                    }
                }
            }
        }

        private static void TestEpp(string pfilePath)
        {
            
            FileInfo existingFile = new FileInfo(pfilePath);

            try
            {
                ExcelPackage package = new ExcelPackage(existingFile);
                int vSheetCount = package.Workbook.Worksheets.Count; //获取总Sheet页

//                ExcelWorksheet worksheet = package.Workbook.Worksheets[1];//选定 指定页
//
//                int maxColumnNum = worksheet.Dimension.End.Column;//最大列
//                int minColumnNum = worksheet.Dimension.Start.Column;//最小列
//
//                int maxRowNum = worksheet.Dimension.End.Row;//最小行
//                int minRowNum = worksheet.Dimension.Start.Row;//最大行
//                Console.WriteLine(minColumnNum.ToString() + " " + maxColumnNum.ToString());
//                Console.WriteLine(minRowNum.ToString() + " " + maxRowNum.ToString());
//                DataTable vTable = new DataTable();
//                DataColumn vC;
//                for (int j = 1; j <= maxColumnNum; j++)
//                {
//                    vC = new DataColumn("A_" + j, typeof(string));
//                    vTable.Columns.Add(vC);
//                }
//                if (maxRowNum > 200)
//                {
//                    maxRowNum = 200;
//                }
//                for (int n = 1; n <= maxRowNum; n++)
//                {
//                    DataRow vRow = vTable.NewRow();
//                    for (int m = 1; m <= maxColumnNum; m++)
//                    {
//                        vRow[m - 1] = worksheet.Cells[n, m].Value;
//                    }
//                    vTable.Rows.Add(vRow);
//                }
//                dataGridView1.DataSource = vTable;
            }
            catch (Exception vErr)
            {
                MessageBox.Show(vErr.Message);
            }
        }
    }
}
